<?php
	function f120($sql){
		//data handle instance
		//set request
		//define format
		//set format
		//define query string
		//process time
		//process fitler
		$dataHandle = new DataHandle();
		$dataHandle->setRequest("120");

/*		$format = array(
			"Start Time" => "dated/m/Y H:i:s",
			"End Time" => "dated/m/Y H:i:s",
			);

		$dataHandle->setFormat($format);*/

		$dataType = getValue($_GET['dataType']);
		$action = getValue($_GET['action']);

		if ($dataType == "json"){
			$error = $dataHandle->processTime($startTime, $endTime);
			if($error == ""){
				$filter = $dataHandle->processFilters($startTime, $endTime, "mix_Produced_mixer","Start_Time");
				$query = getJSONQuery($startTime, $endTime, $filter);
				print($dataHandle->printJSON($sql, $query));
			}
			else
				print($error);
		}
		else if($action == "getYears"){
			$query = getYears();
			print($dataHandle->printJSON($sql, $query));
		}
		else{
			$temp = array();
			$temp['error'] = "120: Bad request";
			print(json_encode($temp));
		}

		unset($dataHandle);

	}

	function getJSONQuery(&$startTime, &$endTime, &$filter){
		$queryString = "
			SELECT     
				mix_Product_Dest.description AS Destination,
				mix_Products.description AS Description,
				mix_Products.product_id AS Recipe,

				ROUND(
					AVG(
						CONVERT(
							float, mix_Produced_Picking.Picking) / 
							CONVERT(
								float,mix_Produced_Picking.batch_no
							)
						)
					,0) AS 'AvgPick',
				
				AVG(
						mix_Produced_Mixer.Draining 
						+ mix_Produced_Mixer.Dumping
					) AS 'AvgDirect',

				ROUND(
					AVG(
						CONVERT(
							float, mix_Produced_Picking.Picking) 
							/ CONVERT(
								float,mix_Produced_Picking.batch_no
							)
						)
				,0) 
				+ AVG(
					mix_Produced_Mixer.Draining 
					+ mix_Produced_Mixer.Dumping
				) as 'SubTot',

				ROUND(
					Count(*) 
					* dbo.mix_Overhead_Div_TotEntries_Period(
				'5/8/2013','10/2/2013'),0) as 'Overhead',

				mix_Mixers.MixingTime as MixingTime,

				ROUND(
					AVG(
						CONVERT(
							float, mix_Produced_Picking.Picking) 
							/ CONVERT(
								float,mix_Produced_Picking.batch_no
							)
						)
				,0) 
				+ AVG(
					mix_Produced_Mixer.Draining 
					+ mix_Produced_Mixer.Dumping
				) 
				+
				ROUND(
					Count(*) 
					* dbo.mix_Overhead_Div_TotEntries_Period(
						'5/8/2013','10/2/2013'
						)
				,0) 
				+ mix_Mixers.MixingTime AS 'Total',
				
				Count(*) as 'Entries'

			FROM mix_Produced_Mixer LEFT OUTER JOIN mix_Produced_Picking 
			ON 	(
					( mix_Produced_Mixer.product_id = mix_Produced_Picking.product_id ) AND 
					( mix_Produced_Mixer.id_shift = mix_Produced_Picking.id_shift )
				)
			LEFT OUTER JOIN	mix_Mixers 
			ON mix_Produced_Mixer.mixer_id = mix_Mixers.mixer_id
			LEFT OUTER JOIN	mix_Products 
			ON mix_Produced_Mixer.product_id = mix_Products.product_id
			LEFT OUTER JOIN mix_Product_Dest 
			ON mix_Products.id_dest = mix_Product_Dest.id_dest";

		$queryString = $queryString.$filter;
		$queryString = $queryString." GROUP BY mix_Products.description, mix_Products.product_id, mix_Mixers.MixingTime, mix_Product_Dest.description ";
		$queryString = $queryString." ORDER BY 'Destination' desc, 'Recipe' asc";

		return $queryString;
	}

	function getYears(){
		$queryString = 
			"SELECT DISTINCT 
				DATEPART(year, modified_Date) AS availableYear 
			FROM mix_Products 
			ORDER BY availableYear desc";
		return $queryString;
	}
?>
